package com.xja.dao.impl;

import com.xja.connutil.ConnUtil;
import com.xja.dao.LwlDao;
import com.xja.entity.*;
import com.xja.entity.pojo.RoomAndBills;
import com.xja.entity.pojo.RoomAndIndent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @projectName: HotelManager
 * @package: com.xja.dao.impl
 * @className: LwlDaoImpl
 * @author: WenLiang0214
 * @description:
 * @date: 2022/5/5 19:59
 */
public class LwlDaoImpl implements LwlDao {
    //根据价格查询
    @Override
    public List<Room> findRoomByPrice(int price) throws SQLException {
        List<Room> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from room where room_price=?");
        ps.setInt(1, price);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Room room = new Room();
            room.setRoomId(rs.getInt("room_id"));
            room.setRoomFloor(rs.getInt("room_floor"));
            room.setRoomKind(rs.getString("room_kind"));
            room.setRoomName(rs.getString("room_name"));
            room.setRoomPrice(rs.getInt("room_price"));
            room.setRoomStatus(rs.getInt("room_status"));
            list.add(room);
        }
        return list;
    }

    //查询所有信息
    @Override
    public List<Room> findRoom() throws SQLException {
        List<Room> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select distinct  room_kind,room_price from room");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Room room = new Room();
            room.setRoomKind(rs.getString("room_kind"));
            room.setRoomPrice(rs.getInt("room_price"));
            list.add(room);
        }
        return list;
    }
    //将订单表的信息转移到登记表


    //查询当前入住的房间编号和名称
    @Override
    public List<Room> findRoomByName(String userName) throws SQLException {
        List<Room> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select distinct  room_id,room_name from register r,user u where u.user_id=r.user_id and u.user_name=?");
        ps.setString(1, userName);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Room room = new Room();
            room.setRoomId(rs.getInt("room_id"));
            room.setRoomName(rs.getString("room_name"));
            list.add(room);
        }
        return list;
    }

    //退房
    @Override
    public Integer checkOutById(int roomId) throws SQLException {
        //根基id修改房间状态
        Connection conn = ConnUtil.getConn();
        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement("update room set room_status=1   where room_id=?");
        ps.setInt(1, roomId);
        int i = ps.executeUpdate();//返回执行的行数
        //根据房间id，从登记表查看登记信息
        PreparedStatement ps0 = conn.prepareStatement("select *  from register where room_id=?");
        ps0.setInt(1, roomId);
        ResultSet rs = ps0.executeQuery();
        rs.next();
        Register register = new Register();
        register.setRegisterId(rs.getInt("register_id"));
        register.setUserId(rs.getInt("user_id"));
        register.setRoomId(rs.getInt("room_id"));
        register.setRoomName(rs.getString("room_name"));
        register.setRegisterDay(rs.getInt("register_day"));
        //根据用户id删除登记表中一条信息
        PreparedStatement ps2 = conn.prepareStatement("delete  from register where user_id=?");
        ps2.setInt(1, register.getUserId());
        ps2.executeUpdate();
        //计算房费
        PreparedStatement ps3 = conn.prepareStatement("select  room_price from room where room_id =?");
        ps3.setInt(1, roomId);
        ResultSet rs1 = ps3.executeQuery();
        rs1.next();
        int room_price = rs1.getInt("room_price");
        int sum = room_price * register.getRegisterDay();
        String selectsum = sum + "";
        int i1 = 0;
        if (i != 0) {
            //创建一张账单，里面有属性需要从查看得到的订单表中获取
            PreparedStatement ps1 = conn.prepareStatement("insert   into   bills (user_id,room_id,select_sum,duration)values (?,?,?,?)");
            ps1.setInt(1, register.getUserId());
            ps1.setInt(2, register.getRoomId());
            ps1.setString(3, selectsum);//应付金额
            ps1.setInt(4, register.getRegisterDay());//入住时常
            i1 = ps1.executeUpdate();
        }
        conn.commit();
        if (i1 != 0) {
            //返回应该支付的金额
            return sum;
        } else {
            return 0;
        }
    }

    //根据用户名查询用户id
    @Override
    public Integer findUserIdByName(String userName) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select user_id from user where user_name = ? ");
        ps.setString(1, userName);
        ResultSet rs = ps.executeQuery();
        rs.next();
        int userId = rs.getInt("user_id");
        return userId;
    }

    //评价反馈，只有在账单信息表中查询到该用户的信息，才可以允许用户评价
    @Override
    public List<RoomAndBills> findBillsById(int userId) throws SQLException {
        List<RoomAndBills> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from bills b ,room r where b.room_id = r.room_id and b.user_id = ?");
        ps.setInt(1, userId);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            RoomAndBills roomAndBills = new RoomAndBills();
            Room room = new Room();
            room.setRoomId(rs.getInt("room_id"));
            room.setRoomPrice(rs.getInt("room_price"));
            room.setRoomName(rs.getString("room_name"));
            room.setRoomKind(rs.getString("room_kind"));
            room.setRoomFloor(rs.getInt("room_floor"));
            room.setRoomStatus(rs.getInt("room_status"));
            roomAndBills.setRoom(room);
            Bills bills = new Bills();
            bills.setBillsId(rs.getInt("bills_id"));
            bills.setDuration(rs.getInt("duration"));
            bills.setUserId(rs.getInt("user_id"));
            bills.setRoomId(rs.getInt("room_id"));
            bills.setSelectSum(rs.getString("select_sum"));
            roomAndBills.setBills(bills);
            list.add(roomAndBills);
        }
        return list;
    }

    //评价功能
    @Override
    public Integer estimate(Comment comment) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("insert  into commet (user_id,room_id,context) values(?,?,?)");
        ps.setInt(1, comment.getUserId());
        ps.setInt(2, comment.getRoomId());
        ps.setString(3, comment.getContext());
        int i = ps.executeUpdate();
        return i;
    }

    //入住前先查询
    @Override
    public List<RoomAndIndent> fidIndent(String findUserName) throws SQLException {
        List<RoomAndIndent> roomAndIndents = new ArrayList<>();
        RoomAndIndent roomAndIndent = new RoomAndIndent();
        Room room = new Room();
        Indent indent = new Indent();
        Connection conn = ConnUtil.getConn();
        //根据id查找Indent对象
        PreparedStatement ps = conn.prepareStatement("select i.* ,r.* from indent i ,user u ,room r where i.user_id=u.user_id and r.room_id = i.room_id and u.user_name=?");
        ps.setString(1, findUserName);
        ResultSet rs1 = ps.executeQuery();
        while (rs1.next()) {
            indent.setCheckTime(rs1.getTime("check-in time"));
            indent.setDuratiom(rs1.getInt("duration"));
            indent.setUserId(rs1.getInt("user_id"));
            indent.setIndentId(rs1.getInt("indent_id"));
            indent.setRemark(rs1.getString("remark"));
            indent.setRoomId(rs1.getInt("room_id"));
            roomAndIndent.setIndent(indent);
            room.setRoomId(rs1.getInt("room_id"));
            room.setRoomStatus(rs1.getInt("room_status"));
            room.setRoomFloor(rs1.getInt("room_floor"));
            room.setRoomKind(rs1.getString("room_kind"));
            room.setRoomName(rs1.getString("room_name"));
            room.setRoomPrice(rs1.getInt("room_price"));
            roomAndIndent.setRoom(room);
            roomAndIndents.add(roomAndIndent);
        }
        return roomAndIndents;
    }

    @Override
    public Integer register(Register register) throws SQLException {
        //根基id修改房间状态
        Connection conn = ConnUtil.getConn();
        conn.setAutoCommit(false);
        PreparedStatement ps0 = conn.prepareStatement("update room set room_status=0   where room_id=?");
        ps0.setInt(1, register.getRoomId());

        //int i = ps0.executeUpdate();//返回执行的行数
        PreparedStatement ps = conn.prepareStatement("insert into register(user_id,intime,room_id,room_name,register_day)values (?,?,?,?,?)");
        ps.setInt(1, register.getUserId());
        long time = register.getInTime().getTime();
        ps.setDate(2, new java.sql.Date(time));
        ps.setInt(3, register.getRoomId());
        ps.setString(4, register.getRoomName());
        ps.setInt(5, register.getRegisterDay());
        int i = ps.executeUpdate();
        conn.commit();
        return i;
    }


/*    public static void main(String[] args) throws SQLException {
        LwlDaoImpl lwlDao = new LwlDaoImpl();
        List<Room> roomByPrice = lwlDao.findRoomByPrice(186);
        for (Room room : roomByPrice) {
            if (room.getRoomStatus()==null){
                System.out.println("12131213");
            }
            System.out.println(room);
        }
    }*/

}
